import time
import wizard
from osv import osv, fields

day_form = '''<?xml version="1.0"?>
<form string="Input box">
    <separator string="Select range of date?"/>
    <newline/>
    <field name="from_date"/>
    <field name="to_date"/>
    <newline/>
    <field name="records"/>
</form>'''

day_field = {
    'from_date': {'string':'From','type':'date', 'required':True},
    'to_date': {'string':'To','type':'date', 'required':True},
    'records':{'string':'Select(s)','type':'selection','selection':[("selected","Selected Project"),("all","All Project")],'default': lambda *a:"selected",'required':True}    
    #'day_due': {'string':'After how many days the payment should be due?', 'type':'integer', 'required':True, 'default': lambda *a: 14},
}

def _action_open_window(self, cr, uid, data, context):
    form = data['form']
    #raise osv.except_osv(_("Error"),_(data['ids']))
    if form['records']=='all':
        cr.execute("CREATE OR REPLACE VIEW account_analytic_summary_analytic_completed_work AS \
                    select pid as id,code,client,name,sum(turnover_vnd) as turnover_vnd,sum(turnover_usd) as turnover_usd,sum(Expense) as Expense \
                    from (Select aaa.parent_id as pid,aaa.id as child_id,aaa1.Code,aaa1.Name,rp.name as Client from account_analytic_account aaa\
                        left join account_analytic_account aaa1 on aaa.parent_id=aaa1.id\
                        left join res_partner rp on rp.id=aaa1.partner_id\
                        where aaa.parent_id>0 and aaa.type='normal' and aaa1.type='view') tmp1\
                    left join\
                    (Select aaa.id,\
                    coalesce(turnover_vnd,0.0) as turnover_vnd,coalesce(turnonver_usd,0.0) as turnover_usd,\
                    coalesce(expense,0.0) as Expense from account_analytic_account aaa\
                    left join sale_order so on aaa.id=project_id\
                    left join (Select analytic_account_id,sum(turnover_vnd) as turnover_vnd,sum(turnover_usd) as turnonver_usd from\
                        (Select analytic_account_id,rc from account_move_line \
                        left join \
                        (Select move_id as mv_id,coalesce(reconcile_id,0)+coalesce(reconcile_partial_id,0) as rc from account_move_line where reconcile_partial_id>0 or reconcile_id>0) tmp\
                        on move_id=mv_id\
                        where analytic_account_id >0 and journal_id=(Select id from account_journal where type='sale' limit 1)) project_to_reconcile\
                        left join \
                        ((Select reconcile_id as reconcile,sum(credit)- sum(debit) as turnover_vnd,\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*credit)-\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*debit) as turnover_usd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_id>0 and date_created between '%s' and '%s' group by reconcile_id)\
                        union\
                        (Select reconcile_partial_id as reconcile,sum(credit)- sum(debit) as turnover_vnd,\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*credit)-\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*debit) as turnover_usd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_partial_id>0 and date_created between '%s' and '%s' group by reconcile_partial_id)) reconcile_to_amount\
                        on rc=reconcile\
                        group by analytic_account_id) turn_over\
                    on aaa.id=turn_over.analytic_account_id\
                    left join (Select analytic_account_id,sum(exp_vnd) as expense from\
                        (Select analytic_account_id,rc,cat from (Select DISTINCT move_id,analytic_account_id,cat,journal_id from account_move_line aml left join\
                            (Select account_id,cat from vwproduct_account vwpa left join  vwproduct_to_budget vwptb\
                                on vwpa.id=vwptb.id\
                            left join (Select abp.id,bc.type as cat from account_budget_post abp left join budget_category bc on categ_id=bc.id) budget_cat\
                                on budget_id=budget_cat.id where cat is not null) account_to_cat\
                            on aml.account_id=account_to_cat.account_id where analytic_account_id>0 and cat='expense' and journal_id=(Select id from account_journal where type='purchase' limit 1)) account_to_cat\
                        left join\
                            (Select move_id as mv_id,coalesce(reconcile_id,0)+coalesce(reconcile_partial_id,0) as rc from account_move_line where reconcile_partial_id>0 or reconcile_id>0) tmp\
                        on move_id=mv_id\
                        where analytic_account_id >0 ) project_to_reconcile\
                        left join \
                        ((Select reconcile_id as reconcile,sum(debit)-sum(credit) as exp_vnd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_id>0 and date_created between '%s' and '%s' group by reconcile_id)\
                        union\
                        (Select reconcile_partial_id as reconcile,sum(debit)-sum(credit) as exp_vnd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_partial_id>0 and date_created between '%s' and '%s' group by reconcile_partial_id)) reconcile_to_amount\
                        on rc=reconcile\
                        group by analytic_account_id) expense\
                    on aaa.id=expense.analytic_account_id\
                where type='normal') tmp2 on id=child_id group by pid,code,name,client" % (form['from_date'],form['to_date'],form['from_date'],form['to_date'],form['from_date'],form['to_date'],form['from_date'],form['to_date']))
    else:
        #raise osv.except_osv(_("TESTING"),_("and aaa.id in (" +','.join(map(str,data['ids']))+")"))
        cr.execute("CREATE OR REPLACE VIEW account_analytic_summary_analytic_completed_work AS \
                    select pid as id,code,client,name,sum(turnover_vnd) as turnover_vnd,sum(turnover_usd) as turnover_usd,sum(Expense) as Expense \
                    from (Select aaa.parent_id as pid,aaa.id as child_id,aaa1.Code,aaa1.Name,rp.name as Client from account_analytic_account aaa\
                        left join account_analytic_account aaa1 on aaa.parent_id=aaa1.id\
                        left join res_partner rp on rp.id=aaa1.partner_id\
                        where aaa.parent_id>0 and aaa.type='normal' and aaa1.type='view') tmp1\
                    left join\
                    (Select aaa.id,\
                    coalesce(turnover_vnd,0.0) as turnover_vnd,coalesce(turnonver_usd,0.0) as turnover_usd,\
                    coalesce(expense,0.0) as Expense from account_analytic_account aaa\
                    left join sale_order so on aaa.id=project_id\
                    left join (Select analytic_account_id,sum(turnover_vnd) as turnover_vnd,sum(turnover_usd) as turnonver_usd from\
                        (Select analytic_account_id,rc from account_move_line \
                        left join \
                        (Select move_id as mv_id,coalesce(reconcile_id,0)+coalesce(reconcile_partial_id,0) as rc from account_move_line where reconcile_partial_id>0 or reconcile_id>0) tmp\
                        on move_id=mv_id\
                        where analytic_account_id >0 and journal_id=(Select id from account_journal where type='sale' limit 1)) project_to_reconcile\
                        left join \
                        ((Select reconcile_id as reconcile,sum(credit)- sum(debit) as turnover_vnd,\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*credit)-\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*debit) as turnover_usd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_id>0 and date_created between '%s' and '%s' group by reconcile_id)\
                        union\
                        (Select reconcile_partial_id as reconcile,sum(credit)- sum(debit) as turnover_vnd,\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*credit)-\
                            sum(fncompute((select currency_id from res_company limit 1),fncurrnametoid('USD'),date_created)*debit) as turnover_usd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_partial_id>0 and date_created between '%s' and '%s' group by reconcile_partial_id)) reconcile_to_amount\
                        on rc=reconcile\
                        group by analytic_account_id) turn_over\
                    on aaa.id=turn_over.analytic_account_id\
                    left join (Select analytic_account_id,sum(exp_vnd) as expense from\
                        (Select analytic_account_id,rc,cat from (Select DISTINCT move_id,analytic_account_id,cat,journal_id from account_move_line aml left join\
                            (Select account_id,cat from vwproduct_account vwpa left join  vwproduct_to_budget vwptb\
                                on vwpa.id=vwptb.id\
                            left join (Select abp.id,bc.type as cat from account_budget_post abp left join budget_category bc on categ_id=bc.id) budget_cat\
                                on budget_id=budget_cat.id where cat is not null) account_to_cat\
                            on aml.account_id=account_to_cat.account_id where analytic_account_id>0 and cat='expense' and journal_id=(Select id from account_journal where type='purchase' limit 1)) account_to_cat\
                        left join\
                            (Select move_id as mv_id,coalesce(reconcile_id,0)+coalesce(reconcile_partial_id,0) as rc from account_move_line where reconcile_partial_id>0 or reconcile_id>0) tmp\
                        on move_id=mv_id\
                        where analytic_account_id >0 ) project_to_reconcile\
                        left join \
                        ((Select reconcile_id as reconcile,sum(debit)-sum(credit) as exp_vnd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_id>0 and date_created between '%s' and '%s' group by reconcile_id)\
                        union\
                        (Select reconcile_partial_id as reconcile,sum(debit)-sum(credit) as exp_vnd\
                        from account_move_line where move_id not in (select move_id from account_invoice where move_id>0) and reconcile_partial_id>0 and date_created between '%s' and '%s' group by reconcile_partial_id)) reconcile_to_amount\
                        on rc=reconcile\
                        group by analytic_account_id) expense\
                    on aaa.id=expense.analytic_account_id\
                where type='normal') tmp2 on id=child_id where pid in (%s) or child_id in (%s) group by pid,code,name,client " % (form['from_date'],form['to_date'],form['from_date'],form['to_date'],form['from_date'],form['to_date'],form['from_date'],form['to_date'],','.join(map(str,data['ids'])),','.join(map(str,data['ids']))))        
    #cr.execute('select default_credit_account_id from account_journal where id=%s', (form['journal_id'],))
    return {
        'name': _('Completed Work'),
        'view_type': 'form',
        'view_mode': 'tree,form',
        'res_model': 'account_analytic_summary.analytic.completed.work',
        'view_id': False,
        'limit':5000000,
        'type': 'ir.actions.act_window'
    }

class wizard_report(wizard.interface):
    states = {
        'init': {
            'actions': [],
            'result': {'type':'form', 'arch':day_form, 'fields':day_field, 'state':[('end','Cancel'), ('report','Ok')]}
        },
        'report': {
            'actions':[],
            #'result': {'type':'report', 'report':'jobongoing', 'state':'end'}
            'result': {'type':'action', 'action':_action_open_window, 'state':'end'}
        }
    }
wizard_report('wizard.analytic.completed.work')
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

